package com.database;
import java.sql.*;
import java.util.ArrayList;

import com.Helper.*;

public class ReservationDB extends Database
{
	int reservationID;
	String firstName = null;
	String lastName = null;
	float paidPrice;
	int paidPoint;
	String meal = null;
	String seat = null;
	String username = null;
	String flightID = null;	
	String status = null;
	
	public ReservationDB()
	{
		tableName = "Reservation";
	}
	
	public ReservationDB(String firstName, String lastName, float paidPrice, int paidPoint,
			 String meal, String seat, String username, String flightID
			)
	{
		tableName = "Reservation";
		
		this.firstName = firstName;
		this.lastName = lastName;
		this.paidPrice = paidPrice;
		this.paidPoint = paidPoint;
		this.meal = meal;
		this.seat = seat;
		this.username = username;
		this.flightID = flightID;
		this.status = "active";
	}

	public void ModFirstName(String s, int id) throws SQLException
	{
		 String sqlString = "UPDATE "+ tableName + " SET firstname = " + AddQuotes(s) + " WHERE reservationID = " + id;
		 Statement stmt = connection.createStatement();
		 stmt.executeUpdate(sqlString);

	     System.out.println(sqlString + " complete");
	}
	
	public void ModLastName(String s, int id) throws SQLException
	{
		String sqlString = "UPDATE "+ tableName + " SET lastname = " + AddQuotes(s) + " WHERE reservationID = " + id;
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sqlString);

	    System.out.println(sqlString + " complete");
	}
	
	public void ModMeal(String s, int id) throws SQLException
	{
		String sqlString = "UPDATE "+ tableName + " SET meal = " + AddQuotes(s) + " WHERE reservationID = " + id;
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sqlString);

	    System.out.println(sqlString + " complete");
	}
	
	public void ModSeat(String s, int id) throws SQLException
	{
		String sqlString = "UPDATE "+ tableName + " SET seat = " + AddQuotes(s) + " WHERE reservationID = " + id;
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sqlString);

	    System.out.println(sqlString + " complete");
	}
	
	public void ModPaidPrice(float price, int id) throws SQLException
	{
		String sqlString = "UPDATE "+ tableName + " SET paidprice = " + price + " WHERE reservationID = " + id;
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sqlString);

	    System.out.println(sqlString + " complete");
	}
	
	public void ModPaidPoint(int point, int id) throws SQLException
	{
		String sqlString = "UPDATE "+ tableName + " SET paidpoint = " + point + " WHERE reservationID = " + id;
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sqlString);

	    System.out.println(sqlString + " complete");
	}
	
	public void ModFlight(String s, int id) throws SQLException
	{
		String sqlString = "UPDATE "+ tableName + " SET flightID = " + AddQuotes(s) + " WHERE reservationID = " + id;
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sqlString);

	    System.out.println(sqlString + " complete");
	}
	
	public ArrayList<DestInfo> GetDestList() throws SQLException
	{
		ArrayList<DestInfo> list = new ArrayList<DestInfo>();
		String sqlString = "select flightID from flight";
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
		{
			DestInfo info = new DestInfo();
			info.flightID = rs.getString("flightID");
			list.add(info);
		}
		
		return list;
	}
	
	
	//Cho's freestyle corner -start
	
	//for Admin Stats
	public ArrayList<ReservationInfo> GetAllReservationInfo() throws SQLException
	{
		ArrayList<ReservationInfo> list = new ArrayList<ReservationInfo>();
	    String sqlString = "SELECT * FROM " + tableName;
	    Statement stmt = connection.createStatement();

	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())
	    {
			ReservationInfo info = new ReservationInfo();
	    	info.num = rs.getInt("reservationID");
	    	info.firstName = rs.getString("firstname");
	    	info.lastName = rs.getString("lastname");
	    	info.paidPrice = rs.getFloat("paidprice");
	    	info.meal = rs.getString("meal");
	    	info.seat = rs.getString("seat");
	    	info.flightID = rs.getString("flightID");
	    	info.username = rs.getString("username");
	    	list.add(info);
	    }
		
		return list;
	}	
	
	public ArrayList<SeatInfo> GetAllSeatList() throws SQLException
	{
		ArrayList<SeatInfo> list = new ArrayList<SeatInfo>();
		String sqlString = "SELECT * FROM " + tableName;
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
			SeatInfo info = new SeatInfo();
	    	list.add(info);
	    }
		
		return list;
	}
	//Cho's freestyle corner -end
	
	public ReservationInfo GetSelectedRes(int resID) throws SQLException
	{
		ReservationInfo info = new ReservationInfo();
		String sqlString = "select * from " + tableName + " where reservationID = " + resID;
		System.out.println(sqlString);
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
	    	info.num = rs.getInt("reservationID");
	    	info.firstName = rs.getString("firstname");
	    	info.lastName = rs.getString("lastname");
	    	info.paidPrice = rs.getFloat("paidprice");
	    	info.paidPoint = rs.getInt("paidPoint");
	    	info.meal = rs.getString("meal");
	    	info.seat = rs.getString("seat");
	    	info.status = rs.getString("status");
	    	info.flightID = rs.getString("flightID");
	    	info.username = rs.getString("username");
	    	info.GetFlightInfo();
	    }
		
		return info;
	}
	
	public void UpdateResStatus(String input_username) throws SQLException
	{
		ArrayList<ReservationInfo> list = GetReservationList(input_username);
		FlightDB db = new FlightDB();
		db.Setup(connection);
		
		for (int i = 0; i < list.size(); i++)
		{		
			if (db.IsArrived(list.get(i).flightID) == true)
			{
				int id = list.get(i).num;
				String resStatus = list.get(i).status;
				
				if (resStatus.equals("active"))
				{		
					String sqlString = "UPDATE "+ tableName + " SET status = 'complete' WHERE reservationID = " + id;
					System.out.println(sqlString);
					Statement stmt = connection.createStatement();
					stmt.executeUpdate(sqlString);
				}
			}
			else if (db.IsOnTime(list.get(i).flightID) == true)
			{
				int id = list.get(i).num;
				String resStatus = list.get(i).status;
				
				if (resStatus.equals("complete"))
				{		
					String sqlString = "UPDATE "+ tableName + " SET status = 'active' WHERE reservationID = " + id;
					System.out.println(sqlString);
					Statement stmt = connection.createStatement();
					stmt.executeUpdate(sqlString);
				}
			}
		}
	}
	
	public void SetInactive(int resID) throws SQLException
	{
		String sqlString = "UPDATE "+ tableName + " SET status = 'inactive' WHERE reservationID = " + resID;
		Statement stmt = connection.createStatement();
		System.out.println(sqlString);
		stmt.executeUpdate(sqlString);    
	}
	
	public ArrayList<ReservationInfo> GetCompletedList(String input_username) throws SQLException
	{
		ArrayList<ReservationInfo> completedList = new ArrayList<ReservationInfo>();
		ArrayList<ReservationInfo> allList = GetAllReservationList(input_username);
		
		for (int i = 0; i < allList.size(); i++)
		{
			String status = allList.get(i).status;
			
			if (status.equals("complete"))
			{
				completedList.add(allList.get(i));
			}
		}
		
		return completedList;
	}
	
	public ArrayList<ReservationInfo> GetReservationList(String input_username) throws SQLException
	{
		ArrayList<ReservationInfo> list = new ArrayList<ReservationInfo>();
		String sqlString = "SELECT * FROM " + tableName + " WHERE username = " + AddQuotes(input_username) + " and status = 'active'";
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
			ReservationInfo info = new ReservationInfo();
	    	info.num = rs.getInt("reservationID");
	    	info.firstName = rs.getString("firstname");
	    	info.lastName = rs.getString("lastname");
	    	info.paidPrice = rs.getFloat("paidprice");
	    	info.paidPoint = rs.getInt("paidPoint");
	    	info.meal = rs.getString("meal");
	    	info.seat = rs.getString("seat");
	    	info.status = rs.getString("status");
	    	info.flightID = rs.getString("flightID");
	    	info.username = rs.getString("username");
	    	info.GetFlightInfo();
	    	info.getImage();
	    	list.add(info);
	    }
		
		return list;
	}
	
	public ArrayList<ReservationInfo> GetReservationListByFlight(String input_flightID) throws SQLException
	{
		ArrayList<ReservationInfo> list = new ArrayList<ReservationInfo>();
		String sqlString = "SELECT * FROM " + tableName + " WHERE flightID = " + AddQuotes(input_flightID) + " and status = 'active'";
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
			ReservationInfo info = new ReservationInfo();
	    	info.num = rs.getInt("reservationID");
	    	info.firstName = rs.getString("firstname");
	    	info.lastName = rs.getString("lastname");
	    	info.paidPrice = rs.getFloat("paidprice");
	    	info.paidPoint = rs.getInt("paidPoint");
	    	info.meal = rs.getString("meal");
	    	info.seat = rs.getString("seat");
	    	info.status = rs.getString("status");
	    	info.flightID = rs.getString("flightID");
	    	info.username = rs.getString("username");
	    	info.GetFlightInfo();
	    	info.getImage();
	    	list.add(info);
	    }
		
		return list;
	}
	
	public ArrayList<ReservationInfo> GetAllReservationList(String input_username) throws SQLException
	{
		ArrayList<ReservationInfo> list = new ArrayList<ReservationInfo>();
		String sqlString = "SELECT * FROM " + tableName + " WHERE username = " + AddQuotes(input_username);
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
			ReservationInfo info = new ReservationInfo();
	    	info.num = rs.getInt("reservationID");
	    	info.firstName = rs.getString("firstname");
	    	info.lastName = rs.getString("lastname");
	    	info.paidPrice = rs.getFloat("paidprice");
	    	info.paidPoint = rs.getInt("paidPoint");
	    	info.meal = rs.getString("meal");
	    	info.seat = rs.getString("seat");
	    	info.status = rs.getString("status");
	    	info.flightID = rs.getString("flightID");
	    	info.username = rs.getString("username");
	    	info.GetFlightInfo();
	    	info.getImage();
	    	list.add(info);
	    }
		
		return list;
	}
	
	public ArrayList<ReservationInfo> GetAllReservationList() throws SQLException
	{
		ArrayList<ReservationInfo> list = new ArrayList<ReservationInfo>();
		String sqlString = "SELECT * FROM " + tableName;
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
			ReservationInfo info = new ReservationInfo();
	    	info.num = rs.getInt("reservationID");
	    	info.firstName = rs.getString("firstname");
	    	info.lastName = rs.getString("lastname");
	    	info.paidPrice = rs.getFloat("paidprice");
	    	info.paidPoint = rs.getInt("paidPoint");
	    	info.meal = rs.getString("meal");
	    	info.seat = rs.getString("seat");
	    	info.status = rs.getString("status");
	    	info.flightID = rs.getString("flightID");
	    	info.username = rs.getString("username");
	    	info.GetFlightInfo();
	    	info.getImage();
	    	list.add(info);
	    }
		
		return list;
	}
	
	public ArrayList<SeatInfo> GetSeatList(String input_username) throws SQLException
	{
		ArrayList<SeatInfo> list = new ArrayList<SeatInfo>();
		String sqlString = "SELECT * FROM " + tableName + " WHERE username = " + AddQuotes(input_username);
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
			SeatInfo info = new SeatInfo();
	    	list.add(info);
	    }
		
		return list;
	}
	
	@Override
	public void Insert() throws SQLException 
	{
		Statement stmt = connection.createStatement();
				
		String sqlString = "INSERT INTO " + tableName + "(firstname,lastname,paidprice,paidpoint,meal,seat,status,username,flightID)" +
				" VALUES (" +
				AddQuotes(firstName) + ", " +
				AddQuotes(lastName) + ", " +
				paidPrice + ", " +
				paidPoint + ", " +
				AddQuotes(meal) + ", " +
				AddQuotes(seat) + ", " +
				AddQuotes(status) + ", " +
				AddQuotes(username) + ", " +
				AddQuotes(flightID) +
				")";
		System.out.println(sqlString);
	    stmt.executeUpdate(sqlString);	     
	}
	
	@Override
	public void Delete(String primaryKey) throws SQLException 
	{
		int id = Integer.parseInt(primaryKey);
		Statement stmt = connection.createStatement();
		
		String sqlString = "DELETE from " + tableName + " WHERE reservationID = " + id;
		stmt.executeUpdate(sqlString);
		System.out.println(sqlString + " complete");
	}

	@Override
	public void Display() throws SQLException 
	{
	    String sqlString = "SELECT * FROM " + tableName;
	    Statement stmt = connection.createStatement();

	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())
	    {
	        System.out.println
	        (
	            rs.getString("reservationID") + " " +
	            rs.getString("firstname") + " " +
	            rs.getString("lastname") + " " +
	            rs.getString("paidprice") + " " +
	            rs.getString("paidpoint") + " " +
	            rs.getString("meal") + " " +
	            rs.getString("seat") + " " +
	            rs.getString("username") + " " +
	            rs.getString("flightID") + " "   
	        );
	    }
	}
}
